﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ProdeBackEnd.Model;
using ProdeBackEnd.Libs;
using System.Data;

namespace ProdeBackEnd.DAO
{
    public class TeamService
    {
        private static TeamService instance;
        private static String TBTeam = "Team";

        private TeamService()
        {}

        public static TeamService getTeamServiceInstance()
        {
            if (instance == null)
            {
                instance = new TeamService();
            }
            return instance;
        }

        public Team getTeamById(long id)
        {
            Team team = null;

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeam + " " +
                            "WHERE ID = " + id;

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
                team = getTeam(teamRow);
            }

            if (team == null){
                throw new BusinessException("301", "No se encontro el elemento solicidado");
            }
            return team;
        }

        /**
         * Obtiene la lista de equipos,  filtro SIN la palabra clave WHERE
         */
        public List<Team> getTeamListFilter(String filter)
        {
            List<Team> teamList = new List<Team>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBTeam + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
                Team team = getTeam(teamRow);
                teamList.Add(team);
            }
            return teamList;
        }

        public Team save(Team team)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL ="";
            if(team.getId() != 0L) //Si es diferente de null es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBTeam + " SET " +
                      "name = '" + team.getName() + "', " +
                      "league = '" + team.getLeague() + "' " +
                      "WHERE id = " + team.getId() + " ";
            }
            else //Si es null es por que es un objeto nuevo
            {
                team.setId(getNewId());
                SQL = "INSERT INTO " + TBTeam + " " +
                       "(id, name, league) VALUES " +
                       "(" + team.getId() + ", '" + team.getName() + "', '" + team.getLeague() + "')";
            }
            
            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return team;
        }

        public void delete(long id)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
           
            SQL = "DELETE  " + TBTeam + "  " +
                    "WHERE id = " + id + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("303", "No se pudo borrar el elemento");
            }
        }

        private Team getTeam(DataRow teamRow)
        {
            Team team = null;

            team = new Team(Convert.ToInt64(teamRow["id"].ToString()), 
                            teamRow["name"].ToString(), 
                            teamRow["league"].ToString(), 
                            teamRow["pathFlag"].ToString());

            return team;
        }

        private long getNewId()
        {
            long id = 0L;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT MAX(id) FROM " + TBTeam + " ";

            DataTable teamTable = conn.executeQuery(SQL);

            foreach (DataRow teamRow in teamTable.Rows)
            {
               id = Convert.ToInt64(teamRow[0].ToString());
            }
            return id;
        }
    }
}